create procedure [dbo].[sp_PIM_GetRecipeUserIngredientDepartByRecipeId]
@RecipeId int
as
begin
	select ru.CookTime,ru.Description,ru.PrepTime,ru.RecDirection,ru.RecipeID,ru.RecipeName,ru.RecipeRating,ru.Serving,
	null as Image,u.UserName,ru.IngredientName,
	(case when(ru.Depart is null) then 'Other' else ru.Depart end)as Depart,ru.LinkImage
	from v_RecipeUserIngredient ru left outer join RecipeImage ri on ru.RecipeId=ri.RecipeId
	left outer join Users u on ru.UserId=u.Userid
	where	ru.RecipeId=@RecipeId
	and		(isShared is null or isShared =1)
end
--exec sp_PIM_GetRecipeUserIngredientDepartByRecipeId 26
go
--------------
create procedure sp_PIM_GetIngredientDepartmentListByMealPlanId
@MealPlanId int
as
begin

	select MealPlanID,
		stuff(
			(
				SELECT DISTINCT CAST(IngredientName AS VARCHAR) + '|'  
				FROM 
				(		
					select m.MealPlanID,md.RecipeId,ri.IngredientName
					from MealPlan m
					left outer join MealPlanDetail md on m.MealPlanId=md.MealPlanId
					inner join RecipeIngredientUser ri on ri.RecipeId=md.RecipeId
					inner join Department d on d.Id=ri.DepartmentId

				)m
				WHERE m.MealPlanID = t.MealPlanID  FOR XML PATH('')
			), 1, 0, '|'
		)as ListIngredient,
		stuff(
			(
				SELECT DISTINCT CAST(Name AS VARCHAR) + ','  
				FROM 
				(		
					select m.MealPlanID,md.RecipeId,d.Name
					from MealPlan m
					left outer join MealPlanDetail md on m.MealPlanId=md.MealPlanId
					inner join RecipeIngredientUser ri on ri.RecipeId=md.RecipeId
					inner join Department d on d.Id=ri.DepartmentId

				)n
				WHERE n.MealPlanID = t.MealPlanID FOR XML PATH('')
			), 1, 0, ','
		)as ListDepartment
	from
	(		
		select m.MealPlanID,ri.IngredientName,d.Name
		from MealPlan m
		left outer join MealPlanDetail md on m.MealPlanId=md.MealPlanId
		inner join RecipeIngredientUser ri on ri.RecipeId=md.RecipeId
		inner join Department d on d.Id=ri.DepartmentId
		where m.MealPlanId=@MealPlanId
	)t
	group by MealPlanID
end
--exec sp_PIM_GetIngredientDepartmentListByMealPlanId 1

